﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Configuration;
using System.Web.Management;
using SchoolManagerMVC.BusinessConcrete.DTO;
using System.IO;
using System.Data.SqlClient;
using System.Web.Security;
using SchoolManagerMVC.BusinessAbstract.Interfaces;
using SchoolManagerMVC.Repository.EntityFramework;
using SchoolManagerMVC.Web.Models;

namespace SchoolManagerMVC.Web.Areas.Administration.Models
{
    public class InitializeDB :Controller, IInitializeBD
    {
        public string sqlConnectionString = ConfigurationManager.ConnectionStrings["Master"].ConnectionString;
        public MembershipCreateStatus createStatus;

        public bool createDB(string sqlFile)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(sqlConnectionString))
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.Connection.Open();
                    FileInfo file = new FileInfo(sqlFile);
                    string script = file.OpenText().ReadToEnd();
                    command.CommandText = script;
                    command.ExecuteNonQuery();
                    command.Connection.Close();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public bool AddMembershipToDB()
        {
            try
            {
                // Installs SQL membership schema to selected database
                SqlServices.Install(
                    // first parameter is database name
                  "SchoolManager",
                    // we'll select installing of all features
                  SqlFeatures.All,
                    // get connection string from web.config
                  ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
                // Informs user that schema is installed successfully
                ViewBag.Message = "Completed";
                return true;

            }
            catch (Exception ex)
            {
                // Something went wrong, get exception message
                ViewBag.Message = ex.ToString();
                return false;
            }
        }

        public bool createTables(string sqlFile)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(sqlConnectionString))
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.Connection.Open();


                    var folderList = Directory.EnumerateFiles(sqlFile);

                    foreach (var curr in folderList)
                    {
                        FileInfo file = new FileInfo(curr);
                        string script = file.OpenText().ReadToEnd();
                        command.CommandText = script;
                        command.ExecuteNonQuery();
                    }

                    command.Connection.Close();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool createKeys(string sqlFile)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(sqlConnectionString))
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = connection;
                    command.Connection.Open();


                    var folderList = Directory.EnumerateFiles(sqlFile);

                    foreach (var curr in folderList)
                    {
                        FileInfo file = new FileInfo(curr);
                        string script = file.OpenText().ReadToEnd();
                        command.CommandText = script;
                        command.ExecuteNonQuery();
                    }

                    command.Connection.Close();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool createRoles()
        {
            try
            {
                Roles.CreateRole(Constants.Admin);
                Roles.CreateRole(Constants.FacultyMember);
                Roles.CreateRole(Constants.OfficeAdmin);
                Roles.CreateRole(Constants.Student);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool addAdminAccount()
        {
            try
            {
                Membership.CreateUser("Administrator", "password", "admin@school.com", null, null, true, null, out createStatus);
                Roles.AddUserToRole("Administrator", Constants.Admin);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool addSampleAccounts(IRepository _repo)
        {
            try
            {
                string[] usernames = new string[] {"TomSmith","SallyJones", "JoeBoil","BobRingman","DanielSuth","SuzyLoverington","JakeNewt","SamKrenshaw","DaveMcAllen","CarrolDaniels","MeganShwartz",
                    "LizNepal","PeterCooper","JaneGoodall","JuliePedington","LennyKravitz","KimStubin","KirkWellmaker","TreverLords","JimHelper"
                };

                string[] emails = new string[]{"tom.smith@school.com","SallyJones@school.com", "JoeBoil@school.com","BobRingman@school.com","DanielSuth@school.com","SuzyLoverington@school.com","JakeNewt@school.com",
                    "SamKrenshaw@school.com","DaveMcAllen@school.com","CarrolDaniels@school.com","MeganShwartz@school.com","LizNepal@school.com","PeterCooper@school.com","JaneGoodall@school.com",
                    "JuliePedington@school.com","LennyKravitz@school.com","KimStubin@school.com", "KirkWellmaker@school.com","TreverLords@school.com","JimHelper@school.com"};

                string[] firstNames = new string[] { "Tom", "Sally", "Joe", "Bob", "Daniel", "Suzy", "Jake", "Sam", "Dave", "Carrol", "Megan", "Liz", "Peter", "Jane", "Julie", "Lenny", "Kim", "Kirk", "Trever", "Jim" };

                string[] lastNames = new string[]{"Smith","Jones","Boil","Ringman","Suth","Loverington","Newt", "Krenshaw","McAllen","Daniels","Shwartz","Nepal","Cooper","Goodall","Pedington","Kravitz","Stubin",
                    "Wellmaker","Lords","Helper"};

                int[] ages = new int[] { 19, 34, 21, 44, 23, 55, 22, 60, 21, 45, 23, 33, 25, 61, 26, 34, 27, 45, 21, 37, 23, 39, 24, 41, 25, 53, 16 };

                for (int x = 0; x < usernames.Count(); x++)
                {
                    Membership.CreateUser(usernames[x], "password", emails[x], null, null, true, null, out createStatus);
                    if (x % 2 == 0)
                    {
                        _repo.Add<Student>(new Student()
                        {
                            UniqueID = (Guid)Membership.GetUser(usernames[x]).ProviderUserKey,
                            FirstName = firstNames[x],
                            LastName = lastNames[x],
                            Age = ages[x],
                            TuitionOwed = 0
                        });
                        Roles.AddUserToRole(usernames[x], Constants.Student);
                    }
                    else
                    {
                        _repo.Add<FacultyMember>(new FacultyMember()
                        {
                            UniqueID = (Guid)Membership.GetUser(usernames[x]).ProviderUserKey,
                            FirstName = firstNames[x],
                            LastName = lastNames[x],
                            Age = ages[x],
                            Salary = 0
                        });
                        Roles.AddUserToRole(usernames[x], Constants.FacultyMember);
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool addSampleData()
        {
            return true;
        }

        public bool disableDbCreation()
        {
            return true;
        }
    }
}
